Filtering Records Using WHERE in MySQL

Course: JustAcademy MySQL Training

Course Demo: Register for MySQL Course Demo

1. Introduction to Filtering Records Using WHERE

In MySQL, the WHERE clause is used to filter records based on specified conditions. It allows users to retrieve only the rows that match particular requirements instead of displaying every record in a table.

For example, an employee table may contain hundreds of employees working in different departments and earning different salaries. If you want to retrieve only employees working in the IT department, you can use WHERE to filter the records.

The WHERE clause is commonly used with SELECT, UPDATE, and DELETE statements. In this lesson, the primary focus is filtering records with SELECT.

Example

SELECT *
FROM employees
WHERE department = 'IT';

This query retrieves only the employee records whose department is IT.

2. Learning Objectives

• Understand the purpose and syntax of the WHERE clause.
• Filter records using comparison operators.
• Combine conditions using AND, OR, and NOT.
• Filter values within a range using BETWEEN.
• Search for multiple values using IN and NOT IN.
• Search for text patterns using LIKE and NOT LIKE.
• Filter records using IS NULL and IS NOT NULL.
• Apply conditions to dates, numbers, and text values.
• Understand operator precedence and parentheses.
• Write practical and efficient filtering queries.

3. What Is the WHERE Clause?

The WHERE clause specifies a condition that determines which rows a SQL statement processes or returns. In a SELECT query, only rows for which the condition evaluates to TRUE are included in the result.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Explanation

• SELECT specifies the columns to retrieve.
• FROM identifies the table containing the data.
• WHERE specifies the filtering condition.
• condition defines which rows should be returned.

Example

SELECT employee_name, salary
FROM employees
WHERE salary > 50000;

This query returns the names and salaries of employees earning more than 50000.

4. Creating a Sample Database and Table

To understand filtering, create a sample database and an employees table.

Step 1: Create a Database

CREATE DATABASE company_db;

Step 2: Select the Database

USE company_db;

Step 3: Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50),
    joining_date DATE,
    bonus DECIMAL(10,2)
);

Step 4: Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, city, joining_date, bonus)
VALUES
(101, 'Amit Sharma', 'IT', 55000.00, 'Mumbai', '2023-01-15', 5000.00),
(102, 'Priya Patel', 'HR', 45000.00, 'Pune', '2022-06-10', 3000.00),
(103, 'Rahul Verma', 'IT', 65000.00, 'Mumbai', '2021-03-20', 7000.00),
(104, 'Neha Singh', 'Finance', 50000.00, 'Delhi', '2023-08-05', NULL),
(105, 'Karan Mehta', 'Marketing', 40000.00, 'Pune', '2024-02-12', 2000.00),
(106, 'Sneha Joshi', 'HR', 48000.00, 'Mumbai', '2022-11-25', NULL),
(107, 'Arjun Rao', 'Finance', 72000.00, 'Bengaluru', '2020-07-18', 8000.00),
(108, 'Pooja Desai', 'IT', 60000.00, 'Pune', '2024-05-09', 4500.00);

This table contains employee details that can be used to practise WHERE conditions involving text, numbers, dates, and NULL values.

5. Filtering Records Using the Equality Operator

The equality operator (=) checks whether a column value matches a specified value.

Example 1: Find Employees in the IT Department

SELECT *
FROM employees
WHERE department = 'IT';

Example 2: Find Employees Living in Mumbai

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai';

Example 3: Find an Employee by ID

SELECT *
FROM employees
WHERE employee_id = 103;

Text values are generally written inside single quotes. Numeric values, such as employee IDs, do not normally require quotes.

6. Comparison Operators in WHERE

Comparison operators allow you to filter records based on relationships between column values and specified values.

Example 1: Salary Greater Than 50000

SELECT employee_name, salary
FROM employees
WHERE salary > 50000;

Example 2: Salary Less Than or Equal to 45000

SELECT employee_name, salary
FROM employees
WHERE salary <= 45000;

Example 3: Exclude the HR Department

SELECT employee_name, department
FROM employees
WHERE department <> 'HR';

Example 4: Find Employees Earning Exactly 60000

SELECT employee_name, salary
FROM employees
WHERE salary = 60000;

Choose the comparison operator that matches the business requirement. Remember that comparisons with NULL generally evaluate to UNKNOWN rather than TRUE or FALSE.

7. Combining Conditions Using AND

The AND operator combines two or more conditions. A row is returned only when all the conditions evaluate to TRUE.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1
AND condition2;

Example 1: IT Employees Earning More Than 55000

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
AND salary > 55000;

Example 2: Employees from Mumbai Earning at Least 50000

SELECT employee_name, city, salary
FROM employees
WHERE city = 'Mumbai'
AND salary >= 50000;

Example 3: Multiple Conditions

SELECT employee_name, department, salary, city
FROM employees
WHERE department = 'IT'
AND salary > 50000
AND city = 'Mumbai';

In the third example, a record must satisfy all three conditions to appear in the result.

8. Combining Conditions Using OR

The OR operator returns a row when at least one of the specified conditions evaluates to TRUE.

Example 1: Employees in IT or HR

SELECT employee_name, department
FROM employees
WHERE department = 'IT'
OR department = 'HR';

Example 2: Employees Living in Mumbai or Pune

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
OR city = 'Pune';

Example 3: Employees Earning Below 45000 or Above 65000

SELECT employee_name, salary
FROM employees
WHERE salary < 45000
OR salary > 65000;

Use OR when a record can satisfy any one of several alternative conditions.

9. Using NOT to Exclude Records

The NOT operator reverses a condition. It is useful when you need to exclude specific values or identify records that do not match a condition.

Example 1: Exclude Employees in IT

SELECT employee_name, department
FROM employees
WHERE NOT department = 'IT';

Example 2: Exclude Employees Living in Pune

SELECT employee_name, city
FROM employees
WHERE NOT city = 'Pune';

Example 3: Use NOT with LIKE

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'A%';

These conditions do not include rows where the tested value is NULL, because the comparison cannot be evaluated as TRUE for those rows. If NULL values must also be included, add an appropriate IS NULL condition.

10. Filtering Values Within a Range Using BETWEEN

The BETWEEN operator filters values within an inclusive range. The lower and upper boundary values are included.

Syntax

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

Example 1: Salaries Between 45000 and 60000

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 45000 AND 60000;

Example 2: Joining Dates Within 2023

SELECT employee_name, joining_date
FROM employees
WHERE joining_date BETWEEN '2023-01-01' AND '2023-12-31';

Example 3: Find Salaries Outside a Range

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 45000 AND 60000;

BETWEEN is useful for numeric ranges and date ranges. For DATETIME columns, use a half-open interval when filtering a complete day or period to avoid missing records with time components.

11. Filtering Multiple Values Using IN

The IN operator checks whether a value matches any value in a specified list. It can make a query easier to read than multiple OR conditions.

Example 1: Employees in IT, HR, or Finance

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Finance');

Equivalent Query Using OR

SELECT employee_name, department
FROM employees
WHERE department = 'IT'
OR department = 'HR'
OR department = 'Finance';

Example 2: Employees from Selected Cities

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Delhi', 'Bengaluru');

Example 3: Find Specific Employee IDs

SELECT *
FROM employees
WHERE employee_id IN (101, 103, 108);

IN is especially useful when filtering by several predefined values.

12. Excluding Multiple Values Using NOT IN

The NOT IN operator returns rows whose values do not match any value in the specified list.

Example 1: Exclude HR and Finance

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('HR', 'Finance');

Example 2: Exclude Selected Cities

SELECT employee_name, city
FROM employees
WHERE city NOT IN ('Pune', 'Delhi');

Important: If the list or subquery used by NOT IN contains NULL, the condition can evaluate to UNKNOWN and unexpectedly return no rows or fewer rows than intended. Avoid NULL in the list or consider using NOT EXISTS for appropriate subquery filtering.

13. Searching Text Patterns Using LIKE

The LIKE operator filters text values based on a pattern. It is useful when you know part of a name, city, email address, or other text field but do not know the complete value.

LIKE Wildcards

Example 1: Names Beginning with A

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%';

Example 2: Names Ending with a

SELECT employee_name
FROM employees
WHERE employee_name LIKE '%a';

Example 3: Names Containing 'ha'

SELECT employee_name
FROM employees
WHERE employee_name LIKE '%ha%';

Example 4: Names with a Specific Character Pattern

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'N_ha%';

The last query looks for names beginning with N, followed by one character, then ha, and any remaining characters.

Case sensitivity depends on the collation and character comparison rules being used. A leading wildcard, such as '%ha%', can also make efficient index-based searching more difficult.

14. Excluding Text Patterns Using NOT LIKE

The NOT LIKE operator excludes text values that match a specified pattern.

Example 1: Names Not Beginning with A

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'A%';

Example 2: Names Not Containing 'sh'

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE '%sh%';

Rows containing NULL in the tested column are not returned by NOT LIKE unless you explicitly include them in the condition.

15. Filtering NULL Values

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the word NULL stored as text.

15.1 Using IS NULL

Use IS NULL to retrieve rows where a column contains a NULL value.

SELECT employee_name, bonus
FROM employees
WHERE bonus IS NULL;

This query finds employees whose bonus has not been recorded as a value.

15.2 Using IS NOT NULL

Use IS NOT NULL to retrieve rows where a column contains a non-NULL value.

SELECT employee_name, bonus
FROM employees
WHERE bonus IS NOT NULL;

15.3 Incorrect NULL Comparison

SELECT *
FROM employees
WHERE bonus = NULL;

This is not the correct way to test for NULL. Ordinary comparisons with NULL do not evaluate to TRUE or FALSE in the usual way.

Correct Query

SELECT *
FROM employees
WHERE bonus IS NULL;

16. Filtering Records Using Date Conditions

WHERE can filter records based on date values. MySQL date literals are commonly written in the YYYY-MM-DD format.

Example 1: Employees Who Joined After 2023-01-01

SELECT employee_name, joining_date
FROM employees
WHERE joining_date > '2023-01-01';

Example 2: Employees Who Joined on or After 2024-01-01

SELECT employee_name, joining_date
FROM employees
WHERE joining_date >= '2024-01-01';

Example 3: Employees Who Joined Before 2023

SELECT employee_name, joining_date
FROM employees
WHERE joining_date < '2023-01-01';

Example 4: Employees Who Joined During 2022

SELECT employee_name, joining_date
FROM employees
WHERE joining_date >= '2022-01-01'
AND joining_date < '2023-01-01';

This half-open date range is especially useful for DATETIME columns because it includes every time on the applicable dates without relying on a final time value.

17. Filtering with Arithmetic Expressions

You can use arithmetic expressions in WHERE to filter records based on calculated values.

Example 1: Employees Whose Annual Salary Exceeds 700000

SELECT employee_name, salary
FROM employees
WHERE salary * 12 > 700000;

Example 2: Employees Whose Salary Plus Bonus Exceeds 65000

SELECT employee_name, salary, bonus
FROM employees
WHERE salary + bonus > 65000;

In the second example, rows with a NULL bonus will not satisfy the condition because arithmetic involving NULL normally produces NULL. If NULL bonuses should count as zero, use COALESCE.

SELECT employee_name, salary, bonus
FROM employees
WHERE salary + COALESCE(bonus, 0) > 65000;

COALESCE returns the first non-NULL argument, so the expression treats a missing bonus as zero for this calculation.

18. Combining AND and OR with Parentheses

When a query contains several AND and OR conditions, parentheses help control the order in which conditions are evaluated and make the intended logic easier to understand.

Example 1: IT Employees in Mumbai or Pune

SELECT employee_name, department, city
FROM employees
WHERE department = 'IT'
AND (city = 'Mumbai' OR city = 'Pune');

This query requires the employee to belong to IT and to live in either Mumbai or Pune.

Example 2: High-Paid Employees in IT or Finance

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'Finance')
AND salary > 55000;

Example 3: Understand the Difference

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
OR department = 'HR'
AND salary > 50000;

AND has higher precedence than OR in MySQL. Therefore, this query is interpreted as department = 'IT' OR (department = 'HR' AND salary > 50000). Every IT employee may qualify regardless of salary.

To require the salary condition for both departments, use parentheses:

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'HR')
AND salary > 50000;

19. Using WHERE with SELECT and ORDER BY

WHERE filters records, while ORDER BY sorts the records that remain. These clauses are often used together.

Example 1: IT Employees Sorted by Salary

SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;

Example 2: Employees from Mumbai Sorted by Name

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
ORDER BY employee_name ASC;

Example 3: Filter and Limit Results

SELECT employee_name, salary
FROM employees
WHERE salary > 45000
ORDER BY salary DESC
LIMIT 3;

This query filters employees earning more than 45000, sorts them from highest to lowest salary, and returns at most three rows.

20. WHERE vs HAVING

WHERE and HAVING both filter data, but they serve different purposes.

Example Using WHERE

SELECT employee_name, salary
FROM employees
WHERE salary > 50000;

Example Using HAVING

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Using Both WHERE and HAVING

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE salary > 40000
GROUP BY department
HAVING AVG(salary) > 50000;

This query first excludes employees earning 40000 or less, groups the remaining rows by department, and then retains groups whose average salary exceeds 50000.

21. Using WHERE with UPDATE and DELETE

Although WHERE is commonly used with SELECT, it is also important for modifying and deleting records. Incorrect or missing conditions can affect more rows than intended.

21.1 UPDATE with WHERE

This example updates the salary of one employee identified by employee_id.

UPDATE employees
SET salary = 58000
WHERE employee_id = 101;

Without WHERE, an UPDATE statement can modify every row in the table.

21.2 DELETE with WHERE

This example deletes the record for employee_id 105.

DELETE FROM employees
WHERE employee_id = 105;

Without WHERE, a DELETE statement can delete every row in the table. Always verify the condition and affected rows before executing data-changing statements.

22. Common WHERE Errors and Troubleshooting

Error 1: Missing Quotes Around Text Values

Incorrect example:

SELECT *
FROM employees
WHERE department = IT;

Correct example:

SELECT *
FROM employees
WHERE department = 'IT';

Text literals generally require quotes. Without quotes, MySQL may interpret IT as an identifier.

Error 2: Using = NULL

Incorrect example:

SELECT *
FROM employees
WHERE bonus = NULL;

Correct example:

SELECT *
FROM employees
WHERE bonus IS NULL;

Error 3: Incorrect AND and OR Logic

Problem: A query returns unexpected rows because AND and OR are combined without parentheses.

Solution: Use parentheses to clearly group conditions.

SELECT *
FROM employees
WHERE (department = 'IT' OR department = 'HR')
AND salary > 50000;

Error 4: Using LIKE Incorrectly

Incorrect example:

SELECT *
FROM employees
WHERE employee_name LIKE 'A';

This normally matches the exact one-character pattern A rather than all names beginning with A.

Correct example:

SELECT *
FROM employees
WHERE employee_name LIKE 'A%';

Error 5: Unexpected Results with NOT IN

Problem: A NOT IN condition uses a list or subquery containing NULL, so comparisons may evaluate to UNKNOWN.

Solution: Ensure the comparison list excludes NULL or choose an appropriate NOT EXISTS pattern.

Error 6: No Rows Returned

Possible causes:

• The condition does not match any existing values.
• The spelling or formatting of a value is incorrect.
• The selected database or table is incorrect.
• NULL values prevent comparisons from evaluating to TRUE.
• String comparison behavior depends on the column's collation.

Solution: Inspect the table data and test the condition using a simple SELECT query.

SELECT *
FROM employees;

SELECT employee_name, department
FROM employees
WHERE department = 'IT';

23. Best Practices for Using WHERE

• Write precise conditions to retrieve only the required records.
• Use quotes correctly for text and date literals.
• Use IS NULL and IS NOT NULL to test missing values.
• Use parentheses when combining complex AND and OR conditions.
• Use IN when comparing a column against a list of values.
• Use BETWEEN for inclusive ranges.
• Choose LIKE patterns carefully to avoid unintended matches.
• Be aware of NULL behavior with NOT IN and NOT LIKE.
• Use appropriate data types for numeric, text, and date comparisons.
• Consider indexes on frequently filtered columns when working with large tables.
• Avoid applying unnecessary functions to indexed columns when a direct range condition can achieve the same result.
• Test a WHERE condition with SELECT before using it in UPDATE or DELETE.
• Use EXPLAIN to examine the execution plan of complex filtering queries.

24. Practical Project: Employee Record Filtering

Project objective: Practise filtering employee data using different WHERE conditions.

Task 1: Retrieve Employees in the IT Department

SELECT *
FROM employees
WHERE department = 'IT';

Task 2: Retrieve Employees Earning More Than 50000

SELECT employee_name, salary
FROM employees
WHERE salary > 50000;

Task 3: Retrieve Employees from Mumbai or Pune

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Pune');

Task 4: Retrieve Employees Earning Between 45000 and 65000

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 45000 AND 65000;

Task 5: Find Names Beginning with P

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'P%';

Task 6: Find Employees Without a Bonus

SELECT employee_name, bonus
FROM employees
WHERE bonus IS NULL;

Task 7: Find IT Employees Earning More Than 55000

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
AND salary > 55000;

Task 8: Exclude Employees from HR and Finance

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('HR', 'Finance');

Task 9: Find Employees Who Joined During 2023

SELECT employee_name, joining_date
FROM employees
WHERE joining_date >= '2023-01-01'
AND joining_date < '2024-01-01';

Task 10: Find Employees Who Are Not in IT and Earn at Least 45000

SELECT employee_name, department, salary
FROM employees
WHERE department <> 'IT'
AND salary >= 45000;

25. Interview Questions and Answers

Q1. What is the WHERE clause in MySQL?

Answer: WHERE filters rows based on specified conditions. In a SELECT query, it returns only rows for which the condition evaluates to TRUE.

Q2. Can WHERE be used with SELECT?

Answer: Yes. It is commonly used to retrieve records matching particular requirements.

Q3. What is the difference between AND and OR?

Answer: AND requires all combined conditions to be TRUE, while OR requires at least one condition to be TRUE.

Q4. What is BETWEEN used for?

Answer: BETWEEN checks whether a value falls within an inclusive range.

Q5. What is the purpose of IN?

Answer: IN checks whether a value matches any value in a specified list or result set.

Q6. What is the difference between LIKE and =?

Answer: The equality operator checks for a matching value, while LIKE checks whether a text value matches a pattern.

Q7. How do you filter NULL values?

Answer: Use IS NULL to find NULL values and IS NOT NULL to find non-NULL values.

Q8. What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before grouping, whereas HAVING commonly filters grouped results after aggregation.

Q9. Can WHERE be used with UPDATE and DELETE?

Answer: Yes. WHERE limits which rows an UPDATE modifies or a DELETE removes.

Q10. Why are parentheses useful in WHERE?

Answer: Parentheses explicitly group conditions and help control the evaluation order of AND and OR expressions.

Q11. What happens when a condition evaluates to NULL?

Answer: In SQL's three-valued logic, NULL commonly represents UNKNOWN. A WHERE clause returns only rows for which its condition is TRUE.

Q12. How can WHERE queries be optimized?

Answer: Use selective conditions, suitable indexes, appropriate data types, and EXPLAIN to inspect the execution plan.

26. Quick Reference Cheat Sheet

27. Conclusion

The WHERE clause is an essential part of MySQL because it allows you to retrieve only the records that match your requirements. By combining WHERE with comparison operators, logical operators, BETWEEN, IN, LIKE, and NULL checks, you can build accurate and useful queries for real-world applications.

Understanding how conditions interact, how NULL values behave, and how parentheses affect logical expressions will help you avoid common SQL errors. Practise these examples regularly to build confidence in filtering database records.

Learn MySQL with JustAcademy

Strengthen your SQL and database skills through the JustAcademy MySQL Training Course.

To explore the course and register for a demo, visit JustAcademy Course Demo Registration.